﻿IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[medianamik_GetPendingApprobationByGroupId]'))
BEGIN
EXEC('
ALTER PROCEDURE [dbo].[medianamik_GetPendingApprobationByGroupId]
(
	@publishing bit,
	@exactApprobationLevel bit,
	@groupid varchar(1000),
	@culture varchar(6),
	@pagesize int, 
	@pageindex int
)
AS
		
SELECT	*
FROM	(
	SELECT	ROW_NUMBER() OVER(ORDER BY ModifiedOn DESC) as row,
			*
	FROM	(
		SELECT	parent.*, 
				inherited.*
		FROM (
			SELECT	n.NodeInstanceId, 
					n.IsActive, 
					n.IsDeleted, 
					n.Depth, 
					n.Position, 
					n.IsSubEntity, 
					n.ModifiedBy, 
					n.ModifiedOn, 
					n.CreatedBy, 
					n.HasModifications, 
					n.Name, 
					n.CultureName, 
					n.CreatedOn, 
					parent.NodeId, 
					n.ParentNodeId, 
					n.TypeId, 
					n.Flags, 
					max(parent.path) AS path,
					n.ApprobationLevel
			FROM	dbo.vw_medianamik_fullnodeinstance AS n 
					CROSS APPLY dbo.fn_medianamik_GetNodeInheritedApprobationParent(n.nodeid) AS parent
			WHERE	(@culture is null or (n.culturename = @culture or n.culturename = '''')) and n.isdeleted = 0 and n.hasmodifications = 1
			GROUP BY	n.NodeInstanceId, 
						n.IsActive, 
						n.IsDeleted, 
						n.Depth, 
						n.Position, 
						n.IsSubEntity, 
						n.ModifiedBy, 
						n.ModifiedOn, 
						n.CreatedBy, 
						n.ApprobationLevel, 
						n.HasModifications, 
						n.Name, 
						n.CultureName, 
						n.CreatedOn, 
						parent.NodeId, 
						n.ParentNodeId, 
						n.TypeId, 
						n.Flags
		) AS parent 
			CROSS apply dbo.fn_medianamik_GetNodeInheritedApprobationLevel(parent.path, @groupid) AS inherited
		WHERE	(@publishing = 1 and parent.ApprobationLevel = inherited.MaxInheritedApprobationLevel)
		OR		(
					@publishing = 0
				AND ((@exactApprobationLevel = 1 AND  parent.ApprobationLevel = inherited.inheritedapprobationlevel)
				OR	(@exactApprobationLevel = 0 AND parent.ApprobationLevel <= inherited.inheritedapprobationlevel))
			)
	) x
) y
WHERE	Row >= (@PageIndex - 1) * @PageSize + 1 AND Row <= @PageIndex * @PageSize')
END

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[medianamik_GetPendingApprobationByGroupIdCount]'))
BEGIN
EXEC('ALTER PROCEDURE [dbo].[medianamik_GetPendingApprobationByGroupIdCount]
(
	@exactApprobationLevel bit,
	@groupid varchar(1000),
	@culture varchar(6),
	@publishing bit
)
AS
SELECT	COUNT(*) [Count]
FROM (
	SELECT	parent.*, 
			inherited.*
	FROM (
		SELECT	n.NodeInstanceId, 
				n.IsActive, 
				n.IsDeleted, 
				n.Depth, 
				n.Position, 
				n.IsSubEntity, 
				n.ModifiedBy, 
				n.ModifiedOn, 
				n.CreatedBy, 
				n.HasModifications, 
				n.Name, 
				n.CultureName, 
				n.CreatedOn, 
				parent.NodeId, 
				n.ParentNodeId, 
				n.TypeId, 
				n.Flags, 
				max(parent.path) AS path,
				n.ApprobationLevel
		FROM	dbo.vw_medianamik_fullnodeinstance AS n 
				CROSS APPLY dbo.fn_medianamik_GetNodeInheritedApprobationParent(n.nodeid) AS parent
		WHERE	(@culture is null or (n.culturename = @culture or n.culturename = '''')) and n.isdeleted = 0 and n.hasmodifications = 1
		GROUP BY	n.NodeInstanceId, 
					n.IsActive, 
					n.IsDeleted, 
					n.Depth, 
					n.Position, 
					n.IsSubEntity, 
					n.ModifiedBy, 
					n.ModifiedOn, 
					n.CreatedBy, 
					n.ApprobationLevel, 
					n.HasModifications, 
					n.Name, 
					n.CultureName, 
					n.CreatedOn, 
					parent.NodeId, 
					n.ParentNodeId, 
					n.TypeId, 
					n.Flags
	) AS parent 
		CROSS apply dbo.fn_medianamik_GetNodeInheritedApprobationLevel(parent.path, @groupid) AS inherited
	WHERE	(@publishing = 1 and parent.ApprobationLevel = inherited.MaxInheritedApprobationLevel)
	OR		(
				@publishing = 0
			AND ((@exactApprobationLevel = 1 AND  parent.ApprobationLevel = inherited.inheritedapprobationlevel)
			OR	(@exactApprobationLevel = 0 AND parent.ApprobationLevel <= inherited.inheritedapprobationlevel))
		)
) x')
END

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_medianamik_GetNodeApprobationLevel]'))
BEGIN
EXEC('ALTER function [dbo].[fn_medianamik_GetNodeApprobationLevel] 
(
	@nodeid uniqueidentifier,
	@groupIdList varchar(1000)
)
RETURNS @tbl TABLE 
(
	InheritedApprobationLevel INT NOT NULL,
	MaxInheritedApprobationLevel INT NOT NULL
)
AS
BEGIN
	
declare @x XML
select @x = ap.Groups.query(''/groups/group'') from medianamik_NodeApprobation as ap
where ap.NodeId = @nodeid

	-- Créer une table temporaire avec les ID de groupe
	DECLARE @GroupTable table
	(
		GroupID uniqueidentifier
	)
	
	DECLARE @GroupID varchar(100), @Pos int

	SET @groupIdList = LTRIM(RTRIM(@groupIdList))+ '',''
	SET @Pos = CHARINDEX('','', @groupIdList, 1)

	IF REPLACE(@groupIdList, '','', '''') <> ''''
	BEGIN
		WHILE @Pos > 0
		BEGIN
			SET @GroupID = LTRIM(RTRIM(LEFT(@groupIdList, @Pos - 1)))
			IF @GroupID <> ''''
			BEGIN
				INSERT INTO @GroupTable (GroupID) VALUES (CAST(@GroupID AS uniqueidentifier))
			END
			SET @groupIdList = RIGHT(@groupIdList, LEN(@groupIdList) - @Pos)
			SET @Pos = CHARINDEX('','', @groupIdList, 1)

		END
	END	

INSERT INTO @tbl
SELECT max(i), (SELECT COUNT(T.col.value(''@id'', ''uniqueidentifier'')) FROM @x.nodes(''/group'') as T(col))
from (
	select ROW_NUMBER() over(order by id) as i, groupId
	from (
		SELECT T.col.value(''@id'', ''uniqueidentifier'') as groupId, 1 as id
		FROM @x.nodes(''/group'') as T(col)
	) as y
) as z
where groupId IN(SELECT GroupID from @GroupTable)
HAVING MAX(i) > 0

RETURN
END')

END
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_medianamik_GetNodeInheritedApprobationLevel]'))
BEGIN
EXEC('ALTER FUNCTION [dbo].[fn_medianamik_GetNodeInheritedApprobationLevel]
(	
	@ParentNodePath varchar(8000),
	@GroupId varchar(1000)
)
RETURNS TABLE 
AS
RETURN 
(
	select na.NodeId as InheritedFromNodeId, inherited.*
	from medianamik_NodeApprobation as na
	inner join medianamik_Node as n on na.nodeid = n.nodeid
	CROSS APPLY dbo.fn_medianamik_GetNodeApprobationLevel(na.NodeId, @GroupId) as inherited
	where n.path = @ParentNodePath
)')
END